const { execSQL } = require("../db/mysql")
var { getHotelRoomData } = require("./controlDB")
var { lastTimes } = require("../routers/router")
/*
lastQueryTime和getHotelRoomData可以一次查询多次使用，待优化
*/
function lastQueryTime() {//最后有效插卡取电时间
    var sql = ` SELECT registerId,MAX(reqTime) lasttime FROM rest_net_using_inspection rnui WHERE rnui.status=1 GROUP BY registerId`;
    return execSQL(sql).then(data => {
        var arrs = {};
        data.forEach(function (item) {
            var time = item.lasttime;
            var time1 = time.getFullYear() + '-' + (time.getMonth() + 1) + '-' + time.getDate();//处理时间格式
            time1 += (time.getHours().toString().length == 1) ? " 0" : " "; time1 += time.getHours();
            time1 += (time.getMinutes().toString().length == 1) ? ":0" : ":"; time1 += time.getMinutes();
            time1 += ((time.getSeconds().toString()).length == 1) ? ':0' : ':'; time1 += time.getSeconds();
            arrs[item.registerId] = time1;
        });
        return arrs;
    })
}
// lastQueryTime();
function insert_valid_invalid(hotellist, leftTime, rightTime) {//从数据库拿取插卡取电是否成功数据
    // hotelRoomData=hotel_room_card;//本地//获取酒店id、酒店名和房间对应register
    var sql = `SELECT registerId,MAX(CASE STATUS WHEN 0 THEN acount ELSE 0 END ) invalid,SUM(acount) sumcount FROM (
        SELECT registerId,STATUS,COUNT(*) acount FROM rest_net_using_inspection WHERE reqTime>'${leftTime}' AND resTime<'${rightTime}' GROUP BY registerId,STATUS
        ) rnui GROUP BY registerId`//以防万一，排序
        console.log("3",typeof lastTimes);//undefined
        if (typeof lastTimes==='undefined') lastTimes = lastQueryTime();
        console.log("4",typeof lastTimes)
    return Promise.all([execSQL(sql), getHotelRoomData(hotellist), lastTimes]).then(([listData, hotelRoomData, lastTime]) => {//listData插卡取电信息
        var arrs = {};//优化：根据getHotelRoomData来遍历，减少一层循环，而且只需要部分酒店的registerId
        for (var i = 0; i < listData.length; i++) {
            for (var key in hotelRoomData) {
                if (listData[i].registerId in hotelRoomData[key]) {
                    if (arrs[key]) {
                        arrs[key].roomlist.push({
                            'roomname': hotelRoomData[key][listData[i].registerId].roomname,
                            'rest_netId': hotelRoomData[key][listData[i].registerId].rest_netId,
                            'invalid': listData[i].invalid || 0,
                            'sumcount': listData[i].sumcount,
                            'lastValidTime': lastTime[listData[i].registerId] || "全无效"
                        })
                    } else arrs[key] = {
                        // 'hotelId':key,'registerId':listData[i].registerId,
                        'hotelname': hotelRoomData[key].hotelname,
                        'roomnumber': Object.getOwnPropertyNames(hotelRoomData[key]).length - 1,
                        'roomlist': [{
                            'roomname': hotelRoomData[key][listData[i].registerId].roomname,
                            'rest_netId': hotelRoomData[key][listData[i].registerId].rest_netId,
                            'invalid': listData[i].invalid || 0,
                            'sumcount': listData[i].sumcount,
                            'lastValidTime': lastTime[listData[i].registerId] || "全无效"
                        }]
                    };
                    break;
                }
            }
        }
        // console.log(arrs)
        return arrs;//使数据按某种规范格式输出
    }).then(data => {
        var arrs = [];
        for (var key in data) arrs.push(data[key]);
        return arrs;
    }).catch((err) => console.log("insert_key_power", err));
}
// insert_card_for_power2('2,3','2022-07-04 21:02','2022-07-05 21:04').then(data=>console.log(data[0].roomlist));
function readinvalid(hotellist, leftTime, rightTime) {//获取酒店读取插卡数据失败的时间点
    if(!lastTimes) lastTimes=lastQueryTime();
    return Promise.all([getHotelRoomData(hotellist),lastTimes]).then(async ([data, lastTime]) => {
        for (var key in data) {
            var arr = [];
            for (var key2 in data[key]) {
                if (key2 != 'hotelname') arr.push(key2);
            }
            // console.log(data[2]);//hotelId:{'hotelname':,'reId':{roomname,rest_netId}...}
            await Promise.all([readinvalidSQL(arr.join(), leftTime, rightTime), invalidProportion(arr.join(), leftTime, rightTime)]).then(([data2, data3]) => {
                // console.log(data2,data3);//readinvalidSQL和invalidProportion都没有regiId=7
                arr.forEach(value => {
                    data[key][value]['lastValidTime'] = lastTime[value] || "全无效";
                    data[key][value]['invalidProportion'] = data3[value] || 0;
                    data[key][value]['invalidTime'] = data2[value] || [];
                })
            })
        }
        return data;
    }).then(data => {
        var arrs = [];
        for (var key in data) arrs.push(data[key]);
        return arrs;
    })
}
function invalidProportion(regiIdList, leftTime, rightTime) {//每个房间registerId无效占比
    var sql = `SELECT rnui.registerId,MAX(CASE STATUS WHEN 0 THEN acount ELSE 0 END ) invalid,SUM(acount) sumcount FROM (
        SELECT rnui.registerId,rnui.status,COUNT(*) acount FROM rest_net_using_inspection rnui WHERE
         rnui.registerId IN (${regiIdList}) AND reqTime>'${leftTime}' AND rnui.reqTime<'${rightTime}' GROUP BY rnui.registerId,rnui.status
        ) rnui GROUP BY rnui.registerId`;
    return execSQL(sql).then(data => {
        var arrs = {};
        for (var i = 0; i < data.length; i++) {
            arrs[data[i].registerId] = data[i].invalid / data[i].sumcount;
        }
        return arrs;//{'registerId':invalidProportion}
    })
}
function readinvalidSQL(regiIdList, leftTime, rightTime) {//根据regiId读取失效时间点
    var sql = `SELECT rnui.registerId,rnui.reqTime FROM rest_net_using_inspection rnui WHERE rnui.status=0 
        AND rnui.registerId IN (${regiIdList}) AND reqTime>'${leftTime}' AND rnui.reqTime<'${rightTime}' ORDER BY rnui.registerId,rnui.reqTime`;
    return execSQL(sql).then(data => {
        var arrs = {};
        for (var i = 0; i < data.length; i++) {
            if (arrs[data[i].registerId]) arrs[data[i].registerId].push(data[i].reqTime);
            else arrs[data[i].registerId] = [data[i].reqTime];
        }
        return arrs;//{'regiId': [date1,...],...}
    })
}
// readinvalidSQL('2,4,5,6','2022-8-12','2022-8-13').then(data=>console.log(data))
// readinvalid('2,3','2022-8-12','2022-8-13').then(data=>console.log(data[0]))
function readinvalid2(hotellist, leftTime, rightTime) {//测试用例，获取酒店读取插卡数据成功加失败的时间
    console.log("5",typeof lastTimes)
    if (typeof lastTimes==='undefined') lastTimes = lastQueryTime();
    console.log("6",typeof lastTimes)
    return Promise.all([getHotelRoomData(hotellist), lastTimes]).then(async ([data, lastTime]) => {
        var arrs = {};
        for (var key in data) {
            var arr = [];
            for (var key2 in data[key]) {//registerId
                if (key2 != 'hotelname') {
                    arr.push(key2);
                    data[key][key2]['registerId'] = key2;
                }
            }
            // console.log(data[2]);//hotelId:{'hotelname':,'reId':{roomname,rest_netId}...}
            await Promise.all([readinvalidSQL2(arr.join(), leftTime, rightTime), invalidProportion(arr.join(), leftTime, rightTime)]).then(([data2, data3]) => {
                arr.forEach(value => {
                    data[key][value]['lastValidTime'] = lastTime[value] || "全无效";
                    data[key][value]['invalidProportion'] = (typeof data3[value]!=='undefined') ? data3[value] : 1;
                    data[key][value]['invalidTime'] = data2[value] || [];
                })
                arrs[key] = {};
                for (var key2 in data[key]) {
                    if (key2 != 'hotelname') {
                        if (arrs[key]['roomlist']) arrs[key]['roomlist'].push(data[key][key2]);
                        else arrs[key]['roomlist'] = [data[key][key2]];
                    } else arrs[key]['hotelname'] = data[key][key2];
                }
            })
        }
        return arrs;
    }).then(data => {
        var arrs = [];
        for (var key in data) arrs.push(data[key]);
        return arrs;
    })
}
function readinvalidSQL2(regiIdList, leftTime, rightTime) {//根据regiId读取成功和失败时间点
    var sql = `SELECT rnui.registerId,rnui.reqTime,rnui.status FROM rest_net_using_inspection rnui WHERE rnui.registerId IN (${regiIdList}) AND reqTime>'${leftTime}' AND rnui.reqTime<'${rightTime}' ORDER BY rnui.registerId,rnui.reqTime`;
    return execSQL(sql).then(data => {
        var arrs = {};
        for (var i = 0; i < data.length; i++) {
            var time = data[i].reqTime;
            var time1 = time.getFullYear() + '-' + (time.getMonth() + 1) + '-' + time.getDate();//处理时间格式
            time1 += (time.getHours().toString().length == 1) ? " 0" : " "; time1 += time.getHours();
            time1 += (time.getMinutes().toString().length == 1) ? ":0" : ":"; time1 += time.getMinutes();
            time1 += ((time.getSeconds().toString()).length == 1) ? ':0' : ':'; time1 += time.getSeconds();
            if (arrs[data[i].registerId]) arrs[data[i].registerId].push({ 'status': data[i].status, 'time': time1 });
            else arrs[data[i].registerId] = [{ 'status': data[i].status, 'time': time1 }];
        }
        return arrs;//{'regiId': [date1,...],...}
    })
}
function card_data(regiId, left, right) {//根据酒店房间regiId和时间查询插卡次数和持续时间
    var sql = `SELECT rnui.registerId,rnui.inTimes,rnui.inTime,rnui.outTimes,rnui.outTime,rnui.reqTime,rnui.status FROM rest_net_using_inspection rnui WHERE rnui.registerId IN (${regiId}) AND rnui.reqTime>'${left}' AND rnui.reqTime<'${right}'`
    return execSQL(sql).then(data => {
        var arrs = [];
        for (var i = 0; i < data.length; i++) {
            var time = data[i].reqTime;
            var time1 = time.getFullYear();
            time1 += ((time.getMonth()+1).toString().length == 1) ? '-0' : '-'; time1 += (time.getMonth() + 1);
            time1 += (time.getDate().toString().length == 1) ? '-0' : '-'; time1 += time.getDate();//处理时间格式
            time1 += (time.getHours().toString().length == 1) ? " 0" : " "; time1 += time.getHours();
            time1 += (time.getMinutes().toString().length == 1) ? ":0" : ":"; time1 += time.getMinutes();
            time1 += (time.getSeconds().toString().length == 1) ? ':0' : ':'; time1 += time.getSeconds();
            arrs.push({
                'registerId': data[i].registerId,
                'inTimes': data[i].inTimes,
                'inTime': data[i].inTime,
                'outTimes': data[i].outTimes,
                'outTime': data[i].outTime,
                'reqTime': time1,
                'status': data[i].status
            });
        }
        return arrs;
    }).catch(err=>console.log("card_data",err))
}
module.exports = {
    insert_valid_invalid,
    lastQueryTime,
    readinvalid,
    readinvalid2,
    card_data
}